package com.glsc.ngateway.common.oc.repo;

import com.glsc.ngateway.common.oc.domain.OcFilecache;
import com.sun.istack.NotNull;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import org.jetbrains.annotations.Nullable;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

@Repository
public interface OcFilecacheRepository extends JpaRepository<OcFilecache, Long>, JpaSpecificationExecutor<OcFilecache> {

    @Query(value = "with recursive query_system_doc_tree_by_fileid( fileid, name, path, mtime, storageMtime, size, parent, dirLevel, sysRootFileid, sysRootName, mimetype ) " +
            "as ( " +
            "select fileid ,name, path, mtime, storage_mtime as storageMtime, size, parent, 1 as dirLevel, fileid as sysRootFileid, name as sysRootName, mimetype from ( " +
            "select * from oc_filecache " +
            "where parent in ( select fileid from oc_filecache " +
            "where 1=1 " +
            "and (coalesce(:#{#rootFileid}, null) is null OR parent in (:#{#rootFileid} )) " +
            ") " +
            "and (coalesce(:#{#exFileid}, null) is null OR parent not in (:#{#exFileid} )) " +
            "and if( :#{#opName} is not null, name = :#{#opName}, 1=1 ) " +
            "union all " +
            "select * from oc_filecache where parent in ( select fileid from oc_filecache " +
            "where 1=1 " +
            "and (coalesce(:#{#exFileid}, null) is null OR parent in (:#{#exFileid} )) " +
            ") " +
            "and if( :#{#opName} is not null, name = :#{#opName}, 1=1 ) " +
            ") t " +
            "union all " +
            "select a.fileid, a.name, a.path, a.mtime, a.storage_mtime as storageMtime, a.size, a.parent, b.dirLevel+1 as dirLevel, b.sysRootFileid, b.sysRootName, a.mimetype " +
            "from oc_filecache a join query_system_doc_tree_by_fileid b " +
            "on a.parent = b.fileid " +
            "and if( :#{#opName} is not null, b.sysRootName = :#{#opName}, 1=1 ) " +
            "and if( :#{#mimetype} is not null, a.mimetype = :#{#mimetype}, 1=1 ) " +
            "and (coalesce(:#{#exFileNames}, null) is null OR a.name not in (:#{#exFileNames} )) " +
            ") " +
            " " +
            "select * from query_system_doc_tree_by_fileid " +
            "where 1=1 " +
            "and if( :#{#fileName} is not null, name like CONCAT('%',:#{#fileName},'%'), 1=1 ) " +
            "order by dirLevel asc " +
            "", nativeQuery = true)
    List<Map<String, Object>> queryByFileidAndName(@NotNull @Param("rootFileid") List<Long> rootFileid,
                                                   @Nullable @Param("exFileid") List<Long> exFileid,
                                                   @Nullable @Param("opName") String opName,
                                                   @Nullable @Param("mimetype") Integer mimetype,
                                                   @Nullable @Param("fileName") String fileName,
                                                   @Nullable @Param("exFileNames") List<String> exFileName);

    @Query(value = "with recursive query_system_doc_tree_by_fileid( fileid, name, path, mtime, storageMtime, size, parent, dirLevel, sysRootFileid, sysRootName, mimetype ) " +
            "as ( " +
            "select fileid ,name, path, mtime, storage_mtime as storageMtime, size, parent, 1 as dirLevel, fileid as sysRootFileid, name as sysRootName, mimetype from ( " +
            "select * from oc_filecache " +
            "where 1=1 "+
            "and if( :#{#opPath} is not null, path = :#{#opPath}, 1=1 ) " +
            ") t " +
            "union all " +
            "select a.fileid, a.name, a.path, a.mtime, a.storage_mtime as storageMtime, a.size, a.parent, b.dirLevel+1 as dirLevel, b.sysRootFileid, b.sysRootName, a.mimetype " +
            "from oc_filecache a join query_system_doc_tree_by_fileid b " +
            "on a.parent = b.fileid " +
            "and if( :#{#opName} is not null, b.sysRootName = :#{#opName}, 1=1 ) " +
            "and if( :#{#mimetype} is not null, a.mimetype = :#{#mimetype}, 1=1 ) " +
            "and (coalesce(:#{#exFileNames}, null) is null OR a.name not in (:#{#exFileNames} )) " +
            ") " +
            " " +
            "select * from query_system_doc_tree_by_fileid " +
            "where 1=1 " +
            "and if( :#{#fileName} is not null, name like CONCAT('%',:#{#fileName},'%'), 1=1 ) " +
            "order by dirLevel asc " +
            "", nativeQuery = true)
    List<Map<String, Object>> queryByFileidAndNameNew(@NotNull @Param("opPath") String opPath,
                                                   @NotNull @Param("opName") String opName,
                                                   @Nullable @Param("mimetype") Integer mimetype,
                                                   @Nullable @Param("fileName") String fileName,
                                                   @Nullable @Param("exFileNames") List<String> exFileName);

    List<OcFilecache> findAllByFileidIn(List<Long> rootFileids);

    OcFilecache findByPath(String path);

    List<OcFilecache> findAllByPathAndStorageIn(String tmpUrl, List<Integer> allStorages);

    List<OcFilecache> findAllByPathInAndStorageIn(List<String> allSystemPath, List<Integer> allStorages);
}